﻿using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using DynamicChart.Util;
using Microsoft.EntityFrameworkCore;

namespace DynamicChart.Repository;

public class SQLRepository
{
    private readonly AppDbContext _context;

    public SQLRepository(AppDbContext context)
    {
        _context = context;
    }
    
    public async Task<List<Dictionary<string, object>>> ActuatorSQL(string sql)
    {
        var result = new List<Dictionary<string, object>>();

        using (var command = _context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = sql;
            command.CommandType = CommandType.Text;

            _context.Database.OpenConnection();

            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    var row = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string columnName = reader.GetName(i);
                        object value = (reader.IsDBNull(i) ? null : reader.GetValue(i))!;
                        row[columnName] = value;
                    }

                    result.Add(row);
                }
            }
        }

        return result;
    }
}